package cn.edu.zjgsu.ito.service;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import cn.edu.zjgsu.ito.model.Classroom;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class AdminLocManageService {

	/**
	 * 根据上传的EXCEL表格，获取所有教室信息并存入数据库
	 * 黄凯航
	 * @throws IOException 
	 * @throws BiffException 
	 */
	public String readLocationTemplate(String fileName,String newFileName,String filePath) throws BiffException, IOException
	{
		Date date=new Date();
		DateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int i=0; //列
		int j=1; //行
		int k=-1;
		int rows=0;
		int breakRows=1;
		int breakExcel=1;
		int breakK=0;
		int countSuccess=0;
		int countUpdate=0;
		int countFail=0;

		String error="";
		String breakClassroomNo="";
		String type="Location Template（教室模板）";
		String uploader="Admin（教学秘书）";
		String uploadTime=format.format(date);

		boolean intValid1=false;
		boolean intValid2=false;

		Record uploadFileRecord=new Record().set("type", type).set("fileName", fileName).set("newFileName", newFileName)
				.set("filePath", filePath).set("uploader", uploader).set("uploadTime", uploadTime);
		Db.save("upload_file", uploadFileRecord);

		Workbook rwb=null;
		Cell[] cells=new Cell[4];
		InputStream stream=new FileInputStream(filePath+"\\"+newFileName);
		rwb=Workbook.getWorkbook(stream);
		Sheet sheet=rwb.getSheet(0);
		cells[0]=sheet.getCell(0,j);
		rows=sheet.getRows();
		//System.out.println("rows="+rows);

		//检测EXCEL表中所有教师编号是否有冲突
		//提取所有教室编号
		String[] classroomNos=new String[rows-1];
		//System.out.println("教室编号数组大小"+(rows-1));
		while(true)
		{
			breakRows++;
			//System.out.println("目前执行到第"+breakRows+"行");
			if(cells[0].getContents().trim().equals("")||cells[0].getContents()==null)
			{
				//System.out.println("第"+breakRows+"行数据为空");
				if(breakRows==rows)
					break;
				j++;
				cells[0]=sheet.getCell(0, j);
				continue;
			}
			k++;
			classroomNos[k]=cells[0].getContents().trim();
			//System.out.println("所对应的教室编号为"+classroomNos[k]);
			breakK=k;
			if(breakRows==rows)
				break;
			
			j++;
			cells[0]=sheet.getCell(0, j);
		}
		//检测冲突
		for(j=0;j<=breakK;j++)
		{
			if(breakExcel==0)
				break;
			for(k=j+1;k<=breakK;k++)
			{
				if(classroomNos[j].equals(classroomNos[k]))
				{
					//System.out.println("检测到文件内部存在冲突");
					breakExcel=0;
					breakClassroomNo=classroomNos[j];
					break;
				}
			}
		}
		//可以执行入库
		if(breakExcel==1)
		{
			//数据再次初始化
			breakRows=1;
			k=0;
			j=1;
			cells[0]=sheet.getCell(0, j);
			while(true)
			{
				breakRows++;
				//教室编号为空忽略不计
				if(cells[0].getContents()==null||cells[0].getContents().trim().equals(""))
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else
						cells[0]=sheet.getCell(0, j);
					continue;
				}
				//从EXCEL表格获取一行数据
				for(i=1;i<4;i++)
					cells[i]=sheet.getCell(i,j);
				//非空字段为空忽略不计
				if(cells[1].getContents().trim().equals("")||
						cells[2].getContents().trim().equals("")||
						cells[3].getContents().trim().equals("")||
						cells[1].getContents()==null||
						cells[2].getContents()==null||
						cells[3].getContents()==null)
				{
					countFail++;
					//System.out.println("教室编号为"+cells[0].getContents().trim()+"的教室数据导入失败，非空字段存在空值");
					j++;
					if(breakRows==rows)
						break;
					else
						cells[0]=sheet.getCell(0, j);
					continue;
				}
				intValid1=isInt(cells[1].getContents().trim());
				intValid2=isInt(cells[2].getContents().trim());
				if(intValid1==false||intValid2==false)
				{
					//两个int值出现非法格式
					countFail++;
					//System.out.println("教室编号"+cells[0].getContents().trim()+"的教室数据导入失败，int类型值非法");
					j++;
					if(breakRows==rows)
						break;
					else
						cells[0]=sheet.getCell(0, j);
					continue;
				}
				//从目标数据库提取所有记录
				List<Record> classroomInfo=Db.find("select * from classroom");
				//冲突检测开始，若目标教室编号已存在则覆盖原记录
				for(i=0;i<classroomInfo.size();i++)
				{
					Record singleClassroomInfo=classroomInfo.get(i);
					String classroomNo=singleClassroomInfo.getStr("classroomNo");
					if(classroomNo.equals(cells[0].getContents().trim()))
					{
						//冲突存在，跳出检测，执行覆盖操作
						//System.out.println("教室编号为"+classroomNo+"的教室已存在，将执行覆盖操作");
						break;
					}
				}
				//冲突不存在，存入数据库
				if(i==classroomInfo.size())
				{
					Record locationRecord=new Record()
					.set("classroomNo", cells[0].getContents().trim())
					.set("classroomVolume", cells[1].getContents().trim())
					.set("examVolume", cells[2].getContents().trim())
					.set("classroomType", cells[3].getContents().trim());
					Db.save("classroom", locationRecord);
					countSuccess++;
				}
				//冲突存在，执行覆盖操作
				else
				{
					Db.update("update classroom set classroomVolume=?,examVolume=?,classroomType=? where classroomNo=?",
							cells[1].getContents().trim(),
							cells[2].getContents().trim(),
							cells[3].getContents().trim(),
							cells[0].getContents().trim()
							);
					countUpdate++;
				}
				j++;
				if(breakRows==rows)
					break;
				else
					cells[0]=sheet.getCell(0, j);
			}
			//System.out.println("成功导入"+countSuccess+"条记录");
			//System.out.println("成功覆盖"+countUpdate+"条记录");
			//System.out.println("导入失败"+countFail+"条记录");
			error="成功导入"+countSuccess+"条记录;成功覆盖"+countUpdate+"条记录;导入失败"+countFail+"条记录";
			return error;
		}
		else
		{
			//System.out.println("所上传的EXCEL表中存在数据冲突 ——护照号："+breakClassroomNo);
			error="所上传的EXCEL表中存在数据冲突 ——护照号："+breakClassroomNo;	
			return error;
		}

	}
	/**
	 * 单个教室添加方法
	 * @param classroom
	 * @return
	 */
	public String addSingleClassroom(Classroom classroom)
	{
		String classroomNo=classroom.getStr("classroomNo").trim();
		String classroomVolume=String.valueOf(classroom.getInt("classroomVolume")).trim();
		String examVolume=String.valueOf(classroom.getInt("examVolume")).trim();
		String classroomType=classroom.getStr("classroomType").trim();
		boolean intValid1=false;
		boolean intValid2=false;
		String error="";
		if(classroomNo.equals("")||classroomVolume.equals("")
				||examVolume.equals("")||classroomType.equals(""))
		{
			error="请填写所有必填字段！";
			return error;
		}

		intValid1=isInt(classroomVolume);
		intValid2=isInt(examVolume);
		if(intValid1==false||intValid2==false)
		{
			error="容量字段请填写数字";
			return error;
		}
		List<Record> classrooms=Db.find("select * from classroom where classroomNo=?",classroomNo);
		if(classrooms.size()!=0)
		{
			//覆盖
			Db.update("update classroom set classroomVolume=?,examVolume=?,classroomType=? where classroomNo=?"
					,classroomVolume,examVolume,classroomType,classroomNo);
			error="覆盖成功！";
			return error;
		}
		classroom.save();
		error="新增成功！";
		return error;
	}
	/**
	 * 单个教室更新方法
	 * @param classroom
	 * @return
	 */
	public String updateSingleClassroom(Classroom classroom)
	{
		String error="";
		if(String.valueOf(classroom.getInt("classroomVolume")).trim().equals("")||
				String.valueOf(classroom.getInt("examVolume")).trim().equals("")||
				classroom.getStr("classroomType").trim().equals(""))
		{
			error="请填写所有必填信息";
			return error;
		}
		boolean intValid1=false;
		boolean intValid2=false;
		intValid1=isInt(String.valueOf(classroom.getInt("classroomVolume")).trim());
		intValid2=isInt(String.valueOf(classroom.getInt("examVolume")).trim());
		if(intValid1==false||intValid2==false)
		{
			error="容量字段请填写数字";
			return error;
		}
		classroom.update();
		error="修改成功";
		return error;
	}
	/**
	 * 单个教室删除方法
	 * @param classroomNo
	 */
	public void deleteOrNot(String classroomNo)
	{
		int studentTimetable=1;
		int teacherTimetable=1;
		int majorTimetable=1;
		List<Record> stNullOrNot=Db.find("select * from student_timetable where classroomNo=?",classroomNo);
		List<Record> ttNullOrNot=Db.find("select * from teacher_timetable where classroomNo=?",classroomNo);
		List<Record> mtNullOrNot=Db.find("select * from major_timetable where classroomNo=?",classroomNo);
		if(stNullOrNot.size()==0)
			studentTimetable=0;
		if(ttNullOrNot.size()==0)
			teacherTimetable=0;
		if(mtNullOrNot.size()==0)
			majorTimetable=0;
		if(studentTimetable==0&&teacherTimetable==0&&majorTimetable==0)
		{
			//System.out.println("执行删除操作");
			Db.update("delete from classroom where classroomNo=?",classroomNo);
		}
	}

	/**
	 * 整数判断方法
	 * @param str
	 * @return
	 */
	public static boolean isInt(String str){
		try{
			Integer.parseInt(str);
			if(!(Integer.parseInt(str)>=0&&Integer.parseInt(str)<=200))
				return false;
		}catch(Exception e){
			return false;
		}
		return true;
	}
}
